package com.example.esp8266.Library.Sql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import android.util.Log;

public class SqlServer {
    String UserName = "";//用户名
    String Password = "";//密码
    String DataBaseName = "";
    String ip = "";
    String TAG = "SQL_Service";
    Connection con = null;
    boolean isConnected = false; // 新增的连接标志

    public SqlServer(String _Username, String _Password, String _DataBaseName, String _ip) {
        UserName = _Username;
        Password = _Password;
        DataBaseName = _DataBaseName;
        ip = _ip;
    }

    public void connect() {
        try { // 加载驱动程序
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
            con = DriverManager.getConnection(
                    "jdbc:jtds:sqlserver://" + ip + "/" + DataBaseName, UserName,
                    Password);
            isConnected = true; // 连接成功
        } catch (ClassNotFoundException e) {
            System.out.println("加载驱动程序出错");
        } catch (java.sql.SQLException e) {
            System.out.println(e.getMessage());
            Log.e("warning", "错误1");
        } catch (Exception e) {
            System.out.println(e.getMessage());
            Log.e("warning", "错误2");
        }
    }

    public boolean isConnected() {
        return isConnected;
    }

    public void SQLstr(String sql) throws java.sql.SQLException {
        try {
            Statement stmt = con.createStatement();
            stmt.executeQuery(sql);
            stmt.close();
        } catch (java.sql.SQLException e) {
            System.out.println(e.getMessage().toString());
        } finally {
            closeConnection();
        }
    }

    public ResultSet GetResultSet(String sql) {
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = con.createStatement();
            rs = stmt.executeQuery(sql);
        } catch (java.sql.SQLException e) {
            e.printStackTrace();
        }
        return rs;
    }

    public List<Result> getList(String sql) throws java.sql.SQLException {
        List<Result> adList = new ArrayList<Result>();
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                Result myResult = new Result(rs.getString("id"), rs.getString("number"));
                adList.add(myResult);
            }
            rs.close();
            stmt.close();
        } catch (java.sql.SQLException e) {
            System.out.println(e.getMessage().toString());
        } finally {
            closeConnection();
        }
        return adList;
    }

    public int GetNum(String sql) throws java.sql.SQLException {
        int rs = 0;
        try {
            Statement stmt = con.createStatement();
            rs = stmt.executeUpdate(sql);
            stmt.close();
        } catch (java.sql.SQLException e) {
            System.out.println(e.getMessage().toString());
        } finally {
            closeConnection();
        }
        return rs;
    }

    public String Select(String sql, String lieming) throws java.sql.SQLException {
        String reauslt = "";
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                reauslt = rs.getString(lieming);
            }
            rs.close();
            stmt.close();
        } catch (java.sql.SQLException e) {
            System.out.println(e.getMessage().toString());
        } finally {
            closeConnection();
        }
        return reauslt;
    }

    private void closeConnection() {
        if (con != null) {
            try {
                con.close();
            } catch (java.sql.SQLException e) {
                Log.i(TAG, "Close SQL service Fail!");
            }
        }
    }
}

class Result {
    String data1 = "";
    String data2 = "";

    public Result(String _data1, String _data2) {
        super();
        this.data1 = _data1;
        this.data2 = _data2;
    }
}